﻿CREATE PROCEDURE [dbo].[FindStoredProcedureText]
	@dbName NVARCHAR(64), 
	@text NVARCHAR(MAX)
AS
BEGIN
	DECLARE @databases TABLE 
	(
		Name NVARCHAR(MAX) NOT NULL
	)

	INSERT INTO @databases
	SELECT [name]
	FROM [master].[dbo].[sysdatabases]

	IF @dbName IS NOT NULL
	BEGIN
		IF EXISTS (SELECT * FROM @databases WHERE LOWER(Name) = LOWER(@dbName))
		BEGIN
			DELETE FROM @databases
      WHERE LOWER(Name) <> LOWER(@dbName)
		END
		ELSE 
		BEGIN
			RAISERROR (N'Database %s is not exists.', 16, 1, @dbName);
		END
	END

	DECLARE @t TABLE
	(
		Id INT IDENTITY(1,1) NOT NULL,
		DbName NVARCHAR(256) NOT NULL,
		SpName NVARCHAR(256) NOT NULL
	)

	DECLARE @sql NVARCHAR(MAX)

	DECLARE @CURSOR CURSOR
	DECLARE @CURRENTDB NVARCHAR(MAX)

	SET @CURSOR  = CURSOR SCROLL
	FOR
	SELECT [Name] FROM @databases
	OPEN @CURSOR
	FETCH NEXT FROM @CURSOR INTO @CURRENTDB
	WHILE @@FETCH_STATUS = 0
	BEGIN

		SET @sql = N'SELECT ''' + @CURRENTDB + ''', [name]
FROM [' + @CURRENTDB + '].[dbo].syscomments t1
JOIN [' + @CURRENTDB + '].[dbo].sysobjects t2 ON t1.[id] = t2.[id] 
WHERE t2.[type] = ''P'' AND [text] LIKE ''%' + @text + '%''
GROUP BY t2.[name]'

		INSERT INTO @t ([DbName], [SpName])
		EXEC (@sql)

		FETCH NEXT FROM @CURSOR INTO @CURRENTDB
	END
	CLOSE @CURSOR		

	SELECT * FROM @t
	
END